﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Windows.Forms;
using System.IO;

namespace 数据库批量导出到excel
{
    public partial class MSSQL_Export : Form
    {

        static SqlConnection Conn;

        public MSSQL_Export()
        {
            InitializeComponent();
        }

        private void btnShowPass_MouseDown(object sender, MouseEventArgs e)
        {
            this.tbxPassword.PasswordChar = '\0';
        }

        private void btnShowPass_MouseUp(object sender, MouseEventArgs e)
        {
            this.tbxPassword.PasswordChar = '*';
        }

        private void btnConnectDB_Click(object sender, EventArgs e)
        {
            string connectionString = "Data Source="+tbxDataSource.Text+";Database="+tbxDatabase.Text+";User ID="+tbxUserId.Text+";Password="+tbxPassword.Text;
            try
            {
                MSSQL_Export.Conn = new SqlConnection(connectionString);
                MSSQL_Export.Conn.Open();
                MessageBox.Show("连接成功！");
                showTables();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void showTables()
        {
            SqlCommand sql = new SqlCommand("select * from sys.tables", Conn);
            SqlDataReader sd = sql.ExecuteReader();

            while (sd.Read())
            {

                int rowIndex = this.dataGridView1.Rows.Add();
                this.dataGridView1.Rows[rowIndex].Cells[0].Value = sd.GetString(0);
            }
            sd.Close();
        }

        private void btnExportTable_Click(object sender, EventArgs e)
        {
            FolderBrowserDialog fo = new FolderBrowserDialog();
            fo.Description = "请选择一个导出的位置";
            fo.ShowNewFolderButton = true;
            DialogResult diaRes = fo.ShowDialog();
            if (diaRes == System.Windows.Forms.DialogResult.OK)
            {
                foreach (DataGridViewRow dr in this.dataGridView1.SelectedRows)
                {
                    if (dr.Cells[0].Value != null)
                    {
                        exportTable(dr.Cells[0].Value.ToString(), fo.SelectedPath);
                    }
                }
                MessageBox.Show("导出完毕");
            }
            else
            {
                MessageBox.Show("取消导出");
            }

        }

        static void exportTable(string p_tableName, string p_storefolder)
        {
            SqlCommand sql = new SqlCommand("select * from " + p_tableName, Conn);
            SqlDataReader sdr = sql.ExecuteReader();
            List<string> cols = new List<string>();
            string header = "";
            for (int i = 0; i < sdr.FieldCount; i++)
            {
                cols.Add(sdr.GetName(i));
                header += sdr.GetName(i);
                if(i<sdr.FieldCount-1)
                    header += ",";
            }

            FileStream fs = new FileStream(p_storefolder + "\\" + p_tableName + ".csv",
                System.IO.FileMode.Create, System.IO.FileAccess.Write);

            StreamWriter sw = new StreamWriter(fs, System.Text.Encoding.UTF8);

            //添加表头
            sw.WriteLine(header);

            //添加数据
            while (sdr.Read())
            {
                string line = "";
                for (int i = 0; i < sdr.FieldCount; i++)
                {
                    line += sdr.GetValue(i).ToString();
                    if (i < sdr.FieldCount - 1)
                        line += ",";
                }
                sw.WriteLine(line);
            }
            sdr.Close();
            sw.Close();
            fs.Close();

        }

        static string exportSql(string p_sqlString)
        {
            SqlCommand sql = new SqlCommand(p_sqlString, Conn);
            SqlDataReader sdr = sql.ExecuteReader();
            List<string> cols = new List<string>();
            string header = "";
            for (int i = 0; i < sdr.FieldCount; i++)
            {
                cols.Add(sdr.GetName(i));
                header += sdr.GetName(i);
                if (i < sdr.FieldCount - 1)
                    header += ",";
            }
            SaveFileDialog sfd = new SaveFileDialog();
            sfd.Filter = "cvs表格文件|*.csv";
            sfd.AddExtension = true;
            DialogResult diaRes =  sfd.ShowDialog();
            if(diaRes!=DialogResult.OK)
            {
                MessageBox.Show("取消导出");
                return null;
            }
            FileStream fs = new FileStream(sfd.FileName,
                System.IO.FileMode.Create, System.IO.FileAccess.Write);

            StreamWriter sw = new StreamWriter(fs, System.Text.Encoding.UTF8);

            //添加表头
            sw.WriteLine(header);

            //添加数据
            while (sdr.Read())
            {
                string line = "";
                for (int i = 0; i < sdr.FieldCount; i++)
                {
                    line += sdr.GetValue(i).ToString();
                    if (i < sdr.FieldCount - 1)
                        line += ",";
                }
                sw.WriteLine(line);
            }
            sdr.Close();
            sw.Close();
            fs.Close();

            return sfd.FileName;
        }

        private void btnExportSql_Click(object sender, EventArgs e)
        {
            try
            {
                string filename = exportSql(this.tbxSql.Text);
                if (this.chkAutoOpen.Checked)
                {
                    System.Diagnostics.Process.Start(filename);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message.ToString());
            }
        }
        
        
    }
}
